--每年计算年假过程
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_annual_vacation_calculate')
          and type in ('P','PC'))
   drop procedure bd_proc_annual_vacation_calculate
go
CREATE PROCEDURE dbo.bd_proc_annual_vacation_calculate
as
BEGIN
	
	-- 1.遍历人员数据
	-- 2.判断是否存在数据假期数据
	-- 3.不存在添加不用清0  存在重新计算覆盖 保存bd_kq_holidays_time时间银行表 bd_kq_holidays假期历史表
	-- 4.自动工龄+1计算年假期 保存bd_kq_holidays_time时间银行表 bd_kq_holidays假期历史表
	
	-- 定义返回受影响行数结果
	declare @result int = 0
	-- 人员ID
	declare @userSerial varchar(max)	
	-- 时间银行ID
	declare @id varchar(max)
	-- 工龄
	declare @workAge int
	
	--  异常扑捉机制
		BEGIN TRY
		
			CREATE TABLE #user_serial(
				id INT IDENTITY,
				user_serial VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)
			
		-- 开启事务
			BEGIN TRAN
				
				-- 添加要处理的数据到临时表中
				insert into #user_serial
				select user_serial from dt_user where user_type!=51;
				
				-- 定义游标
				DECLARE cur_user CURSOR for select user_serial from #user_serial 
				 
				 -- 打开游标
				OPEN cur_user
				
				-- 开始循环游标变量--
				fetch next from cur_user into @userSerial
				
				-- 提取游标数据，进行业务处理
				while @@fetch_status = 0   
					-- 如果上一次操作成功则继续循环
					BEGIN
					
						-- 修改人员工龄+1
						update dt_user set work_age = isnull(work_age,0)+1 where user_serial = @userSerial;
						
						-- 2.判断是否存在数据假期数据
						select @id = id from bd_view_kq_holidays_time_rowno where user_serial = @userSerial and row_no = 1;
						
						-- 3 获取工龄
						select @workAge = isnull(work_age,0) from dt_user where user_serial = @userSerial;
						
						if @id is null
							-- 不存在 添加时间银行表以及计算工龄+1 计算年假期
							begin
							
								-- 年假期 明细
								INSERT INTO bd_kq_holidays(
									user_serial,
									dept_serial,
									dept_name,
									ho_year,
									ho_month,
									ho_date,
									ho_type,
									deduct_before,
									deduct_usage,
									deduct_remain,
									create_id
								)
								select
									a.user_serial,
									b.dep_serial,
									b.dep_name,
									YEAR (GETDATE()),
									MONTH (GETDATE()),
									CONVERT(varchar(10),GETDATE(),120),
									5,
									case
										when c.vacation_annual is null then 0
										else c.vacation_annual
									end,
									case
										when a.work_age>=1 and 10>a.work_age then 5*8*60
										when a.work_age>=10 and 20>a.work_age then 10*8*60
										when a.work_age>=20 then 15*8*60
										else 0 
									end as deduct_usage,
									case
										when a.work_age>=1 and 10>a.work_age then 5*8*60
										when a.work_age>=10 and 20>a.work_age then 10*8*60
										when a.work_age>=20 then 15*8*60
										else 0 
									end as deduct_remain,
									''
								from dt_user a
								inner join dt_dep b on a.user_dep = b.dep_serial
								left join (
									select
										*
									from bd_view_kq_holidays_time_rowno
									where row_no = 1
								) c on a.user_serial = c.user_serial
								where a.user_serial = @userSerial;
								
								-- 添加年假
								INSERT INTO bd_kq_holidays_time(
									user_serial,
									user_dept,
									time_year,
									time_month,
									vacation_annual,
									operate_id,
									operate_time
								)
								select
										a.user_serial,
										b.dep_serial,
										YEAR(DATEADD(MONTH,-1,dbo.bd_fn_getSettleDate(GETDATE()))),
										MONTH(DATEADD(MONTH,-1,dbo.bd_fn_getSettleDate(GETDATE()))),
										case
											when a.work_age>=1 and 10>a.work_age then 5*8*60
											when a.work_age>=10 and 20>a.work_age then 10*8*60
											when a.work_age>=20 then 15*8*60
											else 0 
										end as vacation_annual,
										'',
										CONVERT(varchar(20),GETDATE(),120)
								from dt_user a
								inner join dt_dep b on a.user_dep = b.dep_serial
								where a.user_serial = @userSerial;
					
							end
						else
							-- 存在 清年假额度添加假期历史表  计算工龄+1 计算年假期
							begin
							
								-- 年假期 明细 额度清0
								INSERT INTO bd_kq_holidays(
									user_serial,
									dept_serial,
									dept_name,
									ho_year,
									ho_month,
									ho_date,
									ho_type,
									deduct_before,
									deduct_usage,
									deduct_remain,
									create_id
								)
								select
									a.user_serial,
									b.dep_serial,
									b.dep_name,
									YEAR (GETDATE()),
									MONTH (GETDATE()),
									CONVERT(varchar(10),GETDATE(),120),
									6,
									case
										when c.vacation_annual is null then 0
										else c.vacation_annual
									end,
									case
										when c.vacation_annual is null then 0
										else c.vacation_annual
									end as deduct_usage,
									0,
									''
								from dt_user a
								inner join dt_dep b on a.user_dep = b.dep_serial
								left join (
									select
										*
									from bd_view_kq_holidays_time_rowno
									where row_no = 1
								) c on a.user_serial = c.user_serial
								where a.user_serial = @userSerial;
								
								-- 修改年假银行数据 额度清0
								UPDATE bd_kq_holidays_time
								set vacation_annual = 0
								where user_serial = @userSerial and id = @id;
										
								-- 年假期 明细 添加额度
								INSERT INTO bd_kq_holidays(
									user_serial,
									dept_serial,
									dept_name,
									ho_year,
									ho_month,
									ho_date,
									ho_type,
									deduct_before,
									deduct_usage,
									deduct_remain,
									create_id
								)
								select
									a.user_serial,
									b.dep_serial,
									b.dep_name,
									YEAR (GETDATE()),
									MONTH (GETDATE()),
									CONVERT(varchar(10),GETDATE(),120),
									5,
									case
										when c.vacation_annual is null then 0
										else c.vacation_annual
									end,
									case
										when a.work_age>=1 and 10>a.work_age then 5*8*60
										when a.work_age>=10 and 20>a.work_age then 10*8*60
										when a.work_age>=20 then 15*8*60
										else 0 
									end as deduct_usage,
									case
										when a.work_age>=1 and 10>a.work_age then 5*8*60
										when a.work_age>=10 and 20>a.work_age then 10*8*60
										when a.work_age>=20 then 15*8*60
										else 0 
									end as deduct_remain,
									''
								from dt_user a
								inner join dt_dep b on a.user_dep = b.dep_serial
								left join (
									select
										*
									from bd_view_kq_holidays_time_rowno
									where row_no = 1
								) c on a.user_serial = c.user_serial
								where a.user_serial = @userSerial;
								
								if @workAge>=1 and 10>@workAge
									begin
										-- 修改年假银行数据
										UPDATE bd_kq_holidays_time
										set vacation_annual = 5*8*60
										where user_serial = @userSerial and id = @id;
									end
								else if @workAge>=10 and 20>@workAge
									begin
										-- 修改年假银行数据
										UPDATE bd_kq_holidays_time
										set vacation_annual = 10*8*60
										where user_serial = @userSerial and id = @id;
									end
								else if @workAge>=20
									begin
										-- 修改年假银行数据
										UPDATE bd_kq_holidays_time
										set vacation_annual = 15*8*60
										where user_serial = @userSerial and id = @id;
									end
		
							end
						FETCH  NEXT FROM cur_user INTO @userSerial
					end

				-- 关闭游标
				CLOSE cur_user
				
				-- 释放游标
				deallocate cur_user

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH

--  清除临时表
	drop table #user_serial
	
END;